﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Text;

namespace SilverAnts.Utilities
{
    public class SqlBuilder
    {
        /// <summary>
        /// 查询视图
        /// </summary>
        public string View { get; set; }
        /// <summary>
        /// 构造
        /// </summary>
        public SqlBuilder()
        {
            
        }
        /// <summary>
        /// 构造 - 根据视图初始化
        /// </summary>
        /// <param name="view"></param>
        public SqlBuilder(string view)
        {
            this.View = view;
        }
        private string _orderBy = "";
        private string _select = "";
        private string _join = "";
        /// <summary>
        /// 参数-索引
        /// </summary>
        private int _index = 0;
        /// <summary>
        /// 条件
        /// </summary>
        private List<KeyValuePair<string, KeyValuePair<string, object>>> _clauses = new List<KeyValuePair<string, KeyValuePair<string, object>>>();

        #region 关键字

        #region SELECT
        public SqlBuilder Select(string columns)
        {
            if (columns != "") {
                _select = "-->>SELECT "+columns;
            }
            return this;
        }
        #endregion

        #region FROM
        
        #endregion

        #region AND
        /// <summary>
        /// 
        /// </summary>
        /// <param name="name"></param>
        /// <param name="op"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        private string Text(string name, string op, object value)
        {
            string text = string.Format("{0} {1} @{2} ", name, op, name);
            if (value == null || string.IsNullOrEmpty(value.ToString()))
            {
                if (op == "=")
                {
                    text = name + " IS NULL";
                }
                if (op == "<>")
                {
                    text = name + " IS NOT NULL";
                }
            }
            return text;
        }
        /// <summary>
        /// AND
        /// </summary>
        /// <param name="name"></param>
        /// <param name="op"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public SqlBuilder And(string name, string op, object value)
        {
            string link = _clauses.Count > 0 ? " AND ({0})" : "{0}";
            return Condition(Text(name, op, value), name, value, link);
        }
        #endregion

        #region OR
        /// <summary>
        /// AND
        /// </summary>
        /// <param name="name"></param>
        /// <param name="op"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public SqlBuilder Or(string name, string op, object value)
        {
            string link = _clauses.Count > 0 ? " OR ({0})" : "{0}";
            return Condition(Text(name, op, value), name, value, link);
        }
        #endregion

        #region IN

        #endregion

        #region LIKE
        /// <summary>
        ///  AND -Like
        /// </summary>
        /// <param name="columnName"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public SqlBuilder Like(string columnName, object value)
        {
            if (value.ToString() == "") return this;
            return And(columnName, "LIKE", "%" + value + "%");
        }
        #endregion

        #region BETWEEN AND

        #endregion

        #region ORDER BY

        /// <summary>
        /// ORDER BY
        /// </summary>
        /// <param name="orderby"></param>
        /// <returns></returns>
        public SqlBuilder OrderBy(string orderby)
        {
            if (orderby != "")
            {
                _orderBy = string.Format(" ORDER BY {0} ", orderby);
            }
            return this;
        }

        /// <summary>
        /// ORDER BY ASC
        /// </summary>
        /// <param name="clause"></param>
        public SqlBuilder OrderByAsc(string name)
        {
            return OrderBy(name + " ASC");
        }
        /// <summary>
        /// ORDER BY DESC
        /// </summary>
        /// <param name="clause"></param>
        public SqlBuilder OrderByDesc(string name)
        {
            return OrderBy(name + " DESC");
        }
        #endregion

        #region JOIN
        public SqlBuilder InnerJoin(string tableName)
        {
            if (tableName != "")
            {
                _join = " INNER JOIN " + tableName;
            }
            return this;
        }
        public SqlBuilder LeftJoin(string tableName)
        {
            if (tableName != "")
            {
                _join = " LEFT JOIN " + tableName;
            }
            return this;
        }
        public SqlBuilder RightJoin(string tableName)
        {
            if (tableName != "")
            {
                _join = " RIGHT JOIN " + tableName;
            }
            return this;
        }
        public SqlBuilder On(string on)
        {
            if (on != "")
            {
                _join = _join + " ON " + on;
            }
            return this;
        }
        #endregion

        #endregion

        #region 公共

        public SqlBuilder Condition(string text, string name, object param, string link = "{0}")
        {
            var key = string.Format(link, text.Replace("@" + name, "@" + name + "_" + _index));
            var value = new KeyValuePair<string, object>(name + "_" + _index, param);
            //添加
            _clauses.Add(new KeyValuePair<string, KeyValuePair<string, object>>(key, value));
            _index++;
            return this;
        }
        
        public Sql ToSql()
        {
            var sql = new Sql() { Text = View + _join };
            if (_clauses.Count > 0)
            {
                sql.Text += " WHERE " + string.Join("", _clauses.Select(o => o.Key).ToArray());
                sql.Parameters = _clauses.Select(o => o.Value).ToArray();
            }
            sql.Text += _orderBy + _select;
            return sql;
        }
        #endregion
    }
    /// <summary>
    /// 数据库语句 - text 和 参数值
    /// </summary>
    public class Sql
    {
        public string Text
        {
            get;
            set;
        }
        public object Parameters
        {
            get;
            set;
        }
    }
    /// <summary>
    /// 
    /// </summary>
    /// <typeparam name="T"></typeparam>
    public class Where<T> : SqlBuilder where T : class
    {
        /// <summary>
        /// AND
        /// </summary>
        public Where<T> And(Expression<Func<T, bool>> exp)
        {
            //And(ClauseExpression.ToClause(exp.Body));
            return this;
        }
        /// <summary>
        /// OR
        /// </summary>
        public Where<T> Or(Expression<Func<T, bool>> exp)
        {
            //Or(ClauseExpression.ToClause(exp.Body));
            return this;
        }
    }
}
